package com.kaikeba.dao.impl;

import com.kaikeba.bean.Express;
import com.kaikeba.dao.BaseExpressDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoImpl implements BaseExpressDao {
    //用与获取全部快递（总数 + 今日新增）  待取件快递（总数 + 今日新增）
    static final String SQL_CONSOLE = "SELECT  COUNT(ID) DATA1_SIZE, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) DATA1_DAY, COUNT(STATUS=0 OR NULL) DATA2_SIZE, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) DATA2_DAY FROM EXPRESS";
    //用于分页查询所有快递,LIMIT 表示分页，ALL表示不分页
    static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
    static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    //根据快递单号查询快递信息
    static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
    //根据取件码查询快递信息
    static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
    //根据用户手机号码查询快递信息
    static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
    //根据录入人手机号码查询快递信息
    static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
    // 快递的修改
    static final String SQL_INSERT = "INSERT INTO EXPRESS(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
    //要修改的跨地的id  @param newExpress 新的快递对象（number,company,username,userPhone）userPhone 更改之后涉及发短信所以不更改暂时
    static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER =?,COMPANY=?,USERNAME=?,STATUS=? WHERE ID=?";
    // 快递的删除
    static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID =?";
    //快递的取件操作
    static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS=1,OUTTIME=NOW(),CODE=NULL WHERE CODE=?";


    /**
     * 用与获取全部快递（总数 + 新增）  代取件快递（总数 + 新增）
     *
     * @return [{size:总数,day :新增},{size: 待取件总数,day :新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                int data2_size = resultSet.getInt("data2_size");
                int data2_day = resultSet.getInt("data2_day");
                Map data1 = new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                data.add(data1);
                data.add(data2);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }


        return data;
    }

    /**
     * 用于查询所有快递
     *
     * @param limit      是否分页标记 true：分页
     * @param offset     SQL语句的起始索引
     * @param pageNumber 每一页查询的数量
     * @return
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Express> data = new ArrayList<>();

        try {
            if(limit) {
                preparedStatement = connection.prepareStatement(SQL_FIND_LIMIT);
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);

            }else {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
            }
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                String sysPhone  = resultSet.getString("sysPhone");
                Express e = new Express(id,number,userName,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return data;
    }

    /**
     * 根据快递单号查询快递
     *
     * @param number 单号
     * @return 查询的快递信息，单号不存在时返回null
     */
    @Override
    public Express findByNumber(String number) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            preparedStatement.setString(1,number);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("userName");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                String sysPhone  = resultSet.getString("sysPhone");
                Express e = new Express(id,number,userName,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 根据快递取件码查询快递
     *
     * @param code 单号
     * @return 查询的快递信息，不存在时返回null
     */
    @Override
    public Express findByCode(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_CODE);
            preparedStatement.setString(1,code);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("userName");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String number = resultSet.getString("number");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                String sysPhone  = resultSet.getString("sysPhone");
                Express e = new Express(id,number,userName,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 根据手机号查询快递
     *
     * @param userPhone
     * @return 查询的快递信息，不存在时返回null
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Express> data = new ArrayList<>();

        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            preparedStatement.setString(1,userPhone);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                String sysPhone  = resultSet.getString("sysPhone");
                Express e = new Express(id,number,userName,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return data;
    }

    /**
     * 根据手机号并且根据快递的状态查询快递
     *
     * @param userPhone
     * @param status
     * @return 查询的快递信息，不存在时返回null
     */
    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {

        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Express> data = new ArrayList<>();

        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            preparedStatement.setString(1,userPhone);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status1 = resultSet.getInt("status");
                String sysPhone  = resultSet.getString("sysPhone");
                if(status1==status) {
                    Express e = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                    data.add(e);
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return data;
    }

    /**
     * 根据录入人查询录入的快递
     *
     * @param sysPhone
     * @return 查询的快递信息，不存在时返回null
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        ArrayList<Express> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_SYSPHONE);
            preparedStatement.setString(1,sysPhone);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("userName");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                Express e = new Express(id,number,userName,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return data;
    }

    /**
     * 快递的录入
     *
     * @param e 快递对象
     * @return
     */
    @Override
    public boolean insert(Express e) throws DuplicateCodeException{
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //INSERT INTO EXPRESS(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";

        try {
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            preparedStatement.setString(1,e.getNumber());
            preparedStatement.setString(2,e.getUserName());
            preparedStatement.setString(3,e.getUserPhone());
            preparedStatement.setString(4,e.getCompany());
            preparedStatement.setString(5,e.getCode());
            preparedStatement.setString(6,e.getSysPhone());
            return preparedStatement.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
//            throwables.printStackTrace();
            System.out.println(throwables.getMessage());
            if(throwables.getMessage().endsWith("for key 'code'")){
                // 是因为取件码重复而导致异常
                DuplicateCodeException e2 = new DuplicateCodeException(throwables.getMessage());
                throw e2;
            }else{
                throwables.printStackTrace();
            }

        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 快递修改
     *
     * @param id         要修改的跨地的id
     * @param newExpress 新的快递对象（number,company,username,userPhone）
     * @return
     */
    @Override
    public boolean update(int id, Express newExpress) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            preparedStatement.setString(1, newExpress.getNumber());
            preparedStatement.setString(2, newExpress.getCompany());
            preparedStatement.setString(3,newExpress.getUserName());
            preparedStatement.setInt(4,newExpress.getStatus());
            preparedStatement.setInt(5,id);
            // UPDATE EXPRESS SET NUMBER =?,COMPANY=?,USERNAME=?,STATUS=? WHERE ID=?";
            return preparedStatement.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }

        return false;
    }

    /**
     * 根据快递id 删除快递
     *
     * @param id 要删除的快递id
     * @return
     */
    @Override
    public boolean delete(int id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 更改快递的状态为 1 ，表示取件完成
     *
     * @param code 要修改的快递单号
     * @return
     */
    @Override
    public boolean updateStatus(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE_STATUS);
            preparedStatement.setString(1,code);
            return preparedStatement.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }
}
